import operator
import time
from osv import osv, fields
from osv.orm import intersect
import tools.sql
from tools import config
from tools.translate import _
import netsvc
import datetime


class sale_order(osv.osv):
    _inherit = 'sale.order'
    _name = 'sale.order'
    _description = 'Quotation'
    
#    def write(self, cr, uid, ids, vals, context=None):
#        --if vals:
#          --  if 'taxes_percent' in vals:
#                if not vals['taxes_percent']:
#                    vals['taxes_percent']=0
#                    raise osv.except_osv("E",vals['taxes_percent'])
#        osv.osv.write(self, cr, uid, ids,vals,context=context)
    
    def create(self, cr, uid, vals, context=None):
        self.pool.get('ir.rule').domain_get.clear_cache(cr.dbname)
        return super(sale_order, self).create(cr, uid, vals, context=context)
        
    def copy(self, cr, uid, id, default=None, context={}):
        if not default:
            default = {}
        default.update({
            'name':False,
            'state': 'draft',
#            'shipped': False,
#            'invoice_ids': [],
#            'picking_ids': [],
            #'name': self._get_new_code(cr,uid),
            'date_order':time.strftime('%Y-%m-%d'),
            'contract_created':False,            
            'q_attached':False,
            'q_attached_be':False,
            'q_attached_bm':False,
            'q_attached_qcombine':False,
            'q_attached_je':False,
            'q_attached_jm':False,
            'q_attached_jcombine':False            
        })
        self.pool.get('ir.rule').domain_get.clear_cache(cr.dbname)
        return super(sale_order, self).copy(cr, uid, id, default, context)
    
    def name_get(self, cr, uid, ids, context={}):
        if not len(ids):
            return []
        reads = self.read(cr, uid, ids, ['name'], context)
        res = []
        for record in reads:
            name = record['name']
            res.append((record['id'], name))
        return res
        
    def _get_new_code(self, cr, uid, context={}):
        cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' || \
            lpad((coalesce(max(substring(name from (prefix).length+4 for padding)::int),0)+1)::text,padding,'0') || '-01'\
            from\
            (Select name,(Select prefix from ir_sequence where code='kdvn_hanoi_quotation' and active=True limit 1) as prefix,\
            (Select padding from ir_sequence where code='kdvn_hanoi_quotation' and active=True limit 1) as padding\
            from sale_order where  name  ~ 'QH([0-9]{1,2})-([0-9]{1,4})-([0-9]{1,2})') vw\
            group by prefix,padding;")
        if cr.rowcount:
            res=cr.fetchone()[0]
        else:
            cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' ||\
                        lpad(1::text,padding,'0') || '-01'\
                        from ir_sequence where code='kdvn_hanoi_quotation' and active=True limit 1")
            res=cr.fetchone()[0]
        return res
    
    def _newcode_suggest(self,cr,uid,context={}):
        selection=[]
        cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' || \
            lpad((coalesce(max(substring(name from (prefix).length+4 for padding)::int),0)+1)::text,padding,'0') || '-01'\
            from\
            (Select name,(Select prefix from ir_sequence where code='kdvn_hanoi_quotation' and active=True limit 1) as prefix,\
            (Select padding from ir_sequence where code='kdvn_hanoi_quotation' and active=True limit 1) as padding\
            from sale_order where name ~ ('QH('|| substring(date_part('year',current_date)::text from 3 for 2) || ')-([0-9]{1,4})-([0-9]{1,2})')) vw\
            group by prefix,padding;")
        if cr.rowcount:
            res=cr.fetchone()[0]
            selection.append((res,res))
        else:
            cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' ||\
                        lpad(1::text,padding,'0') || '-01'\
                        from ir_sequence where code='kdvn_hanoi_quotation' and active=True limit 1")
            if cr.rowcount:
                res=cr.fetchone()[0]
                selection.append((res,res))
#New code HAIPHONG        
        cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' || \
            lpad((coalesce(max(substring(name from (prefix).length+4 for padding)::int),0)+1)::text,padding,'0') || '-01'\
            from\
            (Select name,(Select prefix from ir_sequence where code='kdvn_haiphong_quotation' and active=True limit 1) as prefix,\
            (Select padding from ir_sequence where code='kdvn_haiphong_quotation' and active=True limit 1) as padding\
            from sale_order where name ~  ('QP('|| substring(date_part('year',current_date)::text from 3 for 2) || ')-([0-9]{1,4})-([0-9]{1,2})')) vw\
            group by prefix,padding;")
        if cr.rowcount:
            res=cr.fetchone()[0]
            selection.append((res,res))
        else:
            cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' ||\
                        lpad(1::text,padding,'0') || '-01'\
                        from ir_sequence where code='kdvn_haiphong_quotation' and active=True limit 1")
            if cr.rowcount:
                res=cr.fetchone()[0]
                selection.append((res,res))
#New code HCM
        cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' || \
            lpad((coalesce(max(substring(name from (prefix).length+4 for padding)::int),0)+1)::text,padding,'0') || '-01'\
            from\
            (Select name,(Select prefix from ir_sequence where code='kdvn_hcm_quotation' and active=True limit 1) as prefix,\
            (Select padding from ir_sequence where code='kdvn_hcm_quotation' and active=True limit 1) as padding\
            from sale_order where name ~ ('QS('|| substring(date_part('year',current_date)::text from 3 for 2) || ')-([0-9]{1,4})-([0-9]{1,2})')) vw\
            group by prefix,padding;")
        if cr.rowcount:
            res=cr.fetchone()[0]
            selection.append((res,res))
        else:
            cr.execute("Select prefix || substring(date_part('year',current_date)::text from 3 for 2) || '-' ||\
                        lpad(1::text,padding,'0') || '-01'\
                        from ir_sequence where code='kdvn_hcm_quotation' and active=True limit 1")
            if cr.rowcount:
                res=cr.fetchone()[0]            
                selection.append((res,res))
        return selection
    
    def _check_budgete(self, cr, uid, ids, name, arg, context={}):
        res={}
        for so in self.browse(cr,uid,ids):
            res[so.id]=False
            if so.project_id.type=='normal':
                budget_e_obj=self.pool.get("crossovered.budget.lines")
                budget_e_ids=budget_e_obj.search(cr,uid,[('analytic_account_id','=',so.project_id.id)])
                #raise osv.except_osv("Error",budget_e_ids)
                for budgete in budget_e_obj.browse(cr,uid,budget_e_ids):
                    #raise osv.except_osv("Error",budgete.planned_amount)
                    if budgete.planned_amount>0:
                        res[so.id]=True
                        break
        return res
    
    def _check_budgetm(self, cr, uid, ids, name, arg, context={}):
        res={}
        for so in self.browse(cr,uid,ids):
            res[so.id]=False
            if so.project_id.type=='normal':
                budget_m_obj=self.pool.get("crossovered.budget.m.lines")
                budget_m_ids=budget_m_obj.search(cr,uid,[('analytic_account_id','=',so.project_id.id)])
                #raise osv.except_osv("Error",budget_e_ids)
                for budgetm in budget_m_obj.browse(cr,uid,budget_m_ids):
                    #raise osv.except_osv("Error",budgete.planned_amount)
                    if budgetm.planned_amount>0:
                        res[so.id]=True
                        break
        return res

    def _get_sortstatus(self, cr, uid, ids, field_name, arg, context):
        res = {}
        if ids:
            quo_ids = ",".join(map(str,ids))
            cr.execute("Select \
                            id,case when state='done' then 1 else case when state='draft' then 2 else 3 end end as sort_status\
                        from sale_order where id in (%s)" % quo_ids)
            for id,re in cr.fetchall():
                res[id] = re
        return res
        
    def _amount_approved(self, cr, uid, ids, field_name, arg, context):
        res={}
        for x in self.browse(cr,uid,ids):
            res[x.id]=x.approved_amount+x.amount_tax_approved
        for id in ids:
            res[id] = res.get(id, 0.0)
        return res
    
    def _check_approved_amount(self, cr, uid, ids, field_name, arg, context):
        res={}
        for so in self.browse(cr,uid,ids):
            res[so.id]=so.approved_amount==0 or so.approved_amount==None
        for id in ids:
            res[id]=res.get(id,True) 
        return res
    
    def _get_budget_from_project_e(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            for id in ids:
                res[id] = 0.0
#sum(coalesce(kbdm.budget_amount,0)) as q_prj_budget_amount_m\
#                       left join\
 #                           kdvn_budget_data kbdm on project_m_id = kbdm.project_id\                
            cr.execute("Select \
                            so.id,\
                            sum(coalesce(kbde.budget_amount,0)) as q_prj_budget_amount_e\
                        from\
                            sale_order so\
                        left join\
                            kdvn_budget_data kbde on project_e_id = kbde.project_id\
                        group by so.id")
            for id,total_amount in cr.fetchall():
                res[id] = total_amount
        return res
    
    def _get_budget_from_project_m(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            for id in ids:
                res[id] = 0.0
#sum(coalesce(kbdm.budget_amount,0)) as q_prj_budget_amount_m\
#                       left join\
 #                           kdvn_budget_data kbdm on project_m_id = kbdm.project_id\                
            cr.execute("Select \
                            so.id,\
                            sum(coalesce(kbdm.budget_amount,0)) as q_prj_budget_amount_m\
                        from\
                            sale_order so\
                        left join\
                            kdvn_budget_data kbdm on project_m_id = kbdm.project_id\
                        group by so.id")
            for id,total_amount in cr.fetchall():
                res[id] = total_amount
        return res
       
    def _get_quotation_total(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            list_tmp={'amount_total':0.0,
                      'q_amount_total_e':0.0,
                      'q_amount_total_m':0.0,
                      'approved_amount':0.0,
                      'amount_tax_approved':0.0,
                      'amount_total_approved':0.0}
            for quo in self.browse(cr,uid,ids):
                res[quo.id]=list_tmp
                res[quo.id]['amount_total'] = (quo.amount or 0.0) + (quo.amount_tax or 0.0)
                res[quo.id]['q_amount_total_e'] = (quo.q_amount_e or 0.0) + (quo.q_amount_e_other or 0.0) + (quo.q_amount_e_discount or 0.0)
                res[quo.id]['q_amount_total_m'] = (quo.q_amount_m or 0.0) + (quo.q_amount_m_other or 0.0) + (quo.q_amount_m_discount or 0.0)
                res[quo.id]['approved_amount'] = res[quo.id]['q_amount_total_e'] + res[quo.id]['q_amount_total_m']  
                res[quo.id]['amount_tax_approved'] = res[quo.id]['approved_amount'] * quo.taxes_percent /100.0
                res[quo.id]['amount_total_approved'] = res[quo.id]['approved_amount'] + res[quo.id]['amount_tax_approved']  
        return res
            
    def _fnc_check(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            list_temp={'q_attached': False, 'q_attached_be': False, 'q_attached_bm': False, 'q_attached_je':False,
                  'q_attached_jm': False}
            for id in ids:
                res[id]=list_temp
            acc_set = ",".join(map(str, ids))
            cr.execute("Select \
                            res_id as id,\
                            case when sum(case when q_attached is null or q_attached=False then 0 else 1 end) >0 then 1 else 0 end as q_attached,\
                            case when sum(case when q_attached_be is null or q_attached_be=false then 0 else 1 end) >0 then 1 else 0 end as q_attached_be,\
                            case when sum(case when q_attached_bm is null or q_attached_bm =false then 0 else 1 end)>0 then 1 else 0 end as q_attached_bm,\
                            case when sum(case when q_attached_je is null or q_attached_je=false then 0 else 1 end) >0 then 1 else 0 end as q_attached_je,\
                            case when sum(case when q_attached_jm is null or q_attached_jm=false then 0 else 1 end) >0 then 1 else 0 end as q_attached_jm\
                        from ir_attachment \
                        where res_model='sale.order' and res_id in (%s) group by res_id;" % (acc_set))
        for quo_list_chk in cr.dictfetchall():
            tmp_id=quo_list_chk.pop('id')
            res[tmp_id] =quo_list_chk
        return res
    
    def _get_so(self, cr, uid, ids, context):
        result = {}
        for so in self.pool.get('sale.order').browse(cr, uid, ids, context=context):
            result[so.id] = True
        return result.keys()

    def _cal_exrate_amount_budget(self, cr, uid, ids, name, args, context=None): #Temp
        res = {}
        if ids:
            tmp={'tmp_exrate_e':0.0,
                 'tmp_exrate_m':0.0}
            for quo in self.browse(cr,uid,ids):
                res[quo.id]['tmp_exrate_e'] = 0.0
                res[quo.id]['tmp_exrate_m'] = 0.0
        return res
    
    def _cal_total_budget(self, cr, uid, ids, name, args, context=None): #Temp
        res = {}
        if ids:
            for quo in self.browse(cr,uid,ids):
                res[quo.id]= quo.q_prj_budget_amount_e + quo.q_prj_budget_amount_m
        return res
    
    _columns = {
            'partner_shipping_id': fields.many2one('res.partner.address', 'Shipping Address', readonly=True, states={'draft': [('readonly', False)]}),
            #'project_id':fields.many2one('account.analytic.account', 'Analytic Account'),
            'project_e_id':fields.many2one('kdvn.project', 'Project'),
            'project_m_id':fields.many2one('kdvn.project', 'Project'),
            'contract_id':fields.many2one('kdvn.contract.client','Contract',select=1),
            'contract_created':fields.boolean('Contract Created ?'),
            'pricelist_id': fields.many2one('product.pricelist', 'Pricelist'),
            'currency_id': fields.many2one('res.currency', 'Currency'), #required=True, readonly=True, states={'draft': [('readonly', False)]}
            #'parent_project1':fields.function(_parent_project,method=True,type="",relation="account.analytic.account",string='Parent Project'),
            'description':fields.char('Description',size=256),#,required=True,readonly=True,states={'draft': [('readonly', False)]}),
            'amount':fields.float('Amount',digits=(16,2)),#,required=True,readonly=True,states={'draft': [('readonly', False)]}),
            'quotation_type':fields.selection([('E','E'),('M','M'),('E/M','E/M')],'Quotation Type'),
            'taxes_percent':fields.selection([(0,'0'),(5,'5'),(10,'10')],"VAT (%)",select=2),#,required=True,readonly=True,states={'draft': [('readonly', False)]}),
            'amount_tax':fields.float('Taxes'),#readonly=True,states={'draft': [('readonly', False)]}),
            'project_name':fields.char('Project Name', size=250,select=2),#required=True,readonly=True,states={'draft': [('readonly', False)]}),
            'term_of_payment':fields.char('Payement Term', size=128),#readonly=True,states={'draft': [('readonly', False)]}),
            
            'registered_by':fields.many2one('res.users','Registered By',select=2),#readonly=True,states={'draft': [('readonly', False)]},required=True),
            'site_manager_e_id':fields.many2one('res.users','Site Manager (E)',select=2),#readonly=True,states={'draft': [('readonly', False)]},required=True),
            'site_manager_m_id':fields.many2one('res.users','Site Manager (M)',select=2),#readonly=True,states={'draft': [('readonly', False)]},required=True),
            'project_manager_id':fields.many2one('res.users', 'Prj. Manager (E)',select=2),#readonly=True,states={'draft': [('readonly', False)]},),
            'project_manager_m_id':fields.many2one('res.users','Prj. Manager (M)',select=2),#readonly=True,states={'draft': [('readonly', False)]}),
            'owner_id':fields.many2one('res.partner','Owner',select=2),#,readonly=True,states={'draft': [('readonly', False)]},required=True),
            'location':fields.many2one('res.partner.address','Location'),#readonly=True,states={'draft': [('readonly', False)]}),
            
            'validity_period':fields.char('Validity Date',size=50),#readonly=True,states={'draft': [('readonly', False)]}),
            'completion_date':fields.char('Completion date',size=50),#readonly=True,states={'draft': [('readonly', False)]}),
            'remarks':fields.text('Remarks',select=2),#,states={'draft': [('readonly', False)]})readonly=True,
            'state': fields.selection([
                ('draft', 'Not yet decided'),
                ('done', 'Work Received'),
                ('cancel', 'Cancelled')
                ], 'Quo. Status'),#, readonly=True, select=True),
            'sort_status':fields.function(_get_sortstatus,type='integer',string='Number Status',method=True),
            'name': fields.char('Order Reference', size=64,select=True),#required=True, readonly=True,states={'draft':[('readonly',False)]}),
            'newcode_suggest':fields.selection(_newcode_suggest,'New Code',size=16,store=False),#,readonly=True,states={'draft':[('readonly',False)]}
            'budget_e':fields.function(_check_budgete,string='Budget E.',type="boolean",method=True),
            'budget_m':fields.function(_check_budgetm,string='Budget M.',type="boolean",method=True),
            'amount_total':fields.function(_get_quotation_total,method=True,string="Total",type='float',
                                                digits=(16,2),multi='get_quotation_total',
                                                store=True),
            
            'q_amount_e':fields.float("E Amount"),
            'q_amount_e_other':fields.float("E Amount Other"),
            'q_amount_e_discount':fields.float("E Amount Discount"),
            #fields.float("E Total Amount"),
            'q_amount_total_e':fields.function(_get_quotation_total,method=True,string="E Total Amount",type='float',
                                                digits=(16,2),multi='get_quotation_total',
                                                store=True),
            'q_exrate_e':fields.float("Ex.Rate"),
            'q_exrate_m':fields.float("Ex.Rate"),                                                
                                                #store={'sale.order':(lambda self, cr, uid, ids, c={}: ids, None, 20)}),
            
            'q_amount_m':fields.float("M Amount"),
            'q_amount_m_other':fields.float("M Amount Other"),
            'q_amount_m_discount':fields.float("M Amount Discount"),#fields.float("M Total Amount"),
            'q_amount_total_m':fields.function(_get_quotation_total,method=True,string="M Total Amount",type='float',
                                                digits=(16,2),multi='get_quotation_total',
                                                store=True),
                                                
            'q_prj_budget_amount_e':fields.float('Budget Amt E'),
            'q_prj_budget_amount_m':fields.float('Budget Amt M'),
            'q_prj_budget_total':fields.function(_cal_total_budget,string='Budget Total',method=True,type='float'),
#            'q_prj_budget_amount_e':function(_get_budget_from_project_e,method=True,
#                                                    type='float',digits=(16,2),
#                                                    string='Prj. Budget E'),
#            'q_prj_budget_amount_m':fields.function(_get_budget_from_project_m,method=True,
#                                                    type='float',digits=(16,2),
#                                                    string='Prj. Budget M'),
            
            'approved_amount':fields.function(_get_quotation_total,method=True,string="Approved Amount",type='float',
                                                digits=(16,2),multi='get_quotation_total',
                                                store=True),                                                
            'amount_tax_approved':fields.function(_get_quotation_total,method=True,string="Taxes",type='float',
                                                digits=(16,2),multi='get_quotation_total',
                                                store=True),                                                
            'amount_total_approved':fields.function(_get_quotation_total,method=True,string="Final Amt",type='float',
                                                digits=(16,2),multi='get_quotation_total',
                                                store=True),
                                                #function(_amount_approved,string='Approved Total',method=True,type='float',digits=(16,2)),            
            'description':fields.text('Description'),
            'dateofregistration':fields.date('Date of Registration'),
            'dateofsubmit':fields.date("Date of Submit"),
            
            #'quotation_type':fields.selection([('e','E'),('m',"M"),('me','M&E')],'Type'),
            #'data_attached':fields.boolean('Data Attached'),
#            'quoatation_attached':fields.boolean('Quotation Attached',select=1),
#            'quoatation_budget_e_attached':fields.boolean('Q.Budget E. Attached',select=1),
#            'quoatation_budget_m_attached':fields.boolean('Q.Budget M. Attached',select=1),
#            'job_budget_e_attached':fields.boolean('J.Budget E. Attached',select=1),
#            'job_budget_m_attached':fields.boolean('J.Budget M. Attached',select=1),

            'q_attached':fields.boolean('Quotation',readonly=True,select=1),
            'q_attached_be':fields.boolean('Q.Budget E.',readonly=True,select=1),
            'q_attached_bm':fields.boolean('Q.Budget M.',readonly=True,select=1),
            'q_attached_qcombine':fields.boolean('Q.Budget Combine',readonly=True,select=1),
            'q_attached_je':fields.boolean('J.Budget E.',readonly=True,select=1),
            'q_attached_jm':fields.boolean('J.Budget M.',readonly=True,select=1),
            'q_attached_jcombine':fields.boolean('J.Budget Combine',readonly=True,select=1),
            'negotiating':fields.selection([('Negotiated','Negotiating'),('Fixed','Fixed')],select=1,string='Final Price',store=True),
                    }
    _sql_constraints = [('quotation_number_error_mask', "CHECK (name ilike 'QH__-%%' or name ilike 'QS__-%%' or name ilike 'QP__-%%')",  'KDVN Error: The quotation number must like QPYY-XXXX, QSYY-XXXX, QHYY-XXXX !'),
        ('quotation_number_error_unique',"unique(name)","KDVN Error: The quotation number must be unique !")]     
    _defaults = {  
        #'validity_period': lambda *a: 'Within 15 days after submitted',
        #'term_of_payment':lambda *a:'Later negotiation',
        #'completion_date':lambda *a:'Later negotiation',
        
        'name':lambda *x:'',#_get_new_code,
        'taxes_percent':lambda *x:10,
        'amount_tax':lambda *x:0.0,
        'amount':lambda *x:0.0,
        'quotation_type':lambda *x:'E',
        
        'project_e_id':lambda *x:False,
        'project_m_id':lambda *x:False,
                        
        'q_amount_e':lambda *x:0.0,
        'q_amount_e_other':lambda *x:0.0,
        'q_amount_e_discount':lambda *x:0.0,
        'q_amount_m':lambda *x:0.0,
        'q_amount_m_other':lambda *x:0.0,
        'q_amount_m_discount':lambda *x:0.0,
        'q_prj_budget_amount_e':lambda *x:0.0,
        'q_prj_budget_amount_m':lambda *x:0.0,
        'contract_created':lambda *x:False
        }

    def action_done_draft(self, cr, uid, ids, *args):
        if not len(ids):
            return False
        cr.execute('select id from sale_order_line where order_id in ('+','.join(map(str, ids))+')', ('done',))
        line_ids = map(lambda x: x[0], cr.fetchall())
        self.write(cr, uid, ids, {'state': 'draft'})
        #self.pool.get('sale.order.line').write(cr, uid, line_ids, {'invoiced': False, 'state': 'draft', 'invoice_lines': [(6, 0, [])]})
        wf_service = netsvc.LocalService("workflow")
        for inv_id in ids:
            wf_service.trg_create(uid, 'sale.order', inv_id, cr)
        return True
    
    def action_work_received(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state': 'done'})
        ##self.pool.get('sale.order.line').write(cr, uid, line_ids, {'invoiced': False, 'state': 'draft', 'invoice_lines': [(6, 0, [])]})
        return True    
    
    def action_cancel(self, cr, uid, ids, *args):
        self.write(cr, uid, ids, {'state': 'cancel'})
        ##self.pool.get('sale.order.line').write(cr, uid, line_ids, {'invoiced': False, 'state': 'draft', 'invoice_lines': [(6, 0, [])]})
        return True
    
    def _onchange_amount_tax(self, cr, uid, ids, per,amount,tax_amount=False,approved=False):
        if tax_amount:
            amount_tax=tax_amount
            if not approved:
                amount_tax_approved=tax_amount           
        else:
            amount_tax=per*amount/100.0
            if not approved:
                amount_tax_approved=amount_tax
        amount_total=amount_tax+amount
        if not approved:
            amount_total_approved=amount_total
        if approved:
            val={'amount_total':amount_total,'amount_tax':amount_tax,'amount':amount,
                 'amount_tax_approved':per*approved/100.0,
                 'amount_total_approved':per*approved/100.0+approved}#,'amount_tax_approved':amount_tax_approved,'amount_total_approved':amount_total_approved}
        else:
            val={'amount_total':amount_total,'amount_tax':amount_tax,'amount':amount}
        result={'value':val}
        return result
    
    def _onchange_amount_e(self, cr, uid, ids, tax_per,amount,other,discount,total_amount_m):
        new_e_total_amount = amount+other+discount
        new_approved = total_amount_m + new_e_total_amount
        new_approved_tax = new_approved*tax_per/100.0
        new_total_approved_total = new_approved + new_approved_tax
        val={'q_amount_e':amount,
             'q_amount_e_other':other,
             'q_amount_e_discount':discount,
             'q_amount_total_e':new_e_total_amount,
             'approved_amount':new_approved,
             'amount_tax_approved':new_approved_tax,
             'amount_total_approved':new_total_approved_total}
        result={'value':val} 
        return result

    def _onchange_amount_m(self, cr, uid, ids, tax_per,amount,other,discount,total_amount_e):
        new_m_total_amount = amount+other+discount
        new_approved = total_amount_e + new_m_total_amount
        new_approved_tax = new_approved*tax_per/100.0
        new_total_approved_total = new_approved + new_approved_tax
        val={'q_amount_m':amount,
             'q_amount_m_other':other,
             'q_amount_m_discount':discount,
             'q_amount_total_m':new_m_total_amount,
             'approved_amount':new_approved,
             'amount_tax_approved':new_approved_tax,
             'amount_total_approved':new_total_approved_total}
        result={'value':val} 
        return result
#    def _onchange_amount_approved(self, cr, uid, ids, per,amount,tax_amount=False):
#        if tax_amount:
#            amount_tax_approved=tax_amount           
#        else:
#            amount_tax_approved=per*amount/100.0
#        amount_total_approved=amount_tax_approved+amount
#        val={'amount_tax_approved':amount_tax_approved,'amount_total_approved':amount_total_approved,'approved_amount':amount}
#        result={'value':val}
#        return result
    
    def _onchange_project_id(self, cr, uid, ids, project,customer=False):
#        raise osv.except_osv(_('Invalid action !'), _('Cannot delete Sale Order(s) which are already confirmed !'))
        if not project:
            return {}
        else:
            prj_obj=self.pool.get('account.analytic.account')
            ptype=prj_obj.read(cr,uid,project,['type','partner_id','parent_id','name','job_currency','user_id','prj_m'])
            #raise osv.except_osv("error",project)
            if ptype['type']=='view':
                part_id=ptype['partner_id'][0]
                #raise osv.except_osv("error",part)                
                addr = self.pool.get('res.partner').address_get(cr, uid, [part_id], ['delivery', 'invoice', 'contact','location'])
                #addr_location=self.pool.get('res.partner').address_get(cr, uid, [part_id])
                part = self.pool.get('res.partner').browse(cr, uid, part_id)
                try:
                    project_e=ptype['user_id']
                except:
                    project_e=False
                try:
                    project_m=ptype['prj_m']
                except:
                    project_m=False
                pricelist = part.property_product_pricelist and part.property_product_pricelist.id or False
                payment_term = part.property_payment_term and part.property_payment_term.id or False
                fiscal_position = part.property_account_position and part.property_account_position.id or False
                val = {'owner_id':part_id,'location':addr['location'],'project_name':ptype['name'],'project_manager_id':project_e,'project_manager_m_id':project_m}
            elif ptype['type']=='normal':
                part_id=ptype['partner_id'][0]
                try:
                    curr_id=ptype['job_currency'][0]
                except:
                    curr_id=False
                if part_id:
                #raise osv.except_osv("error",part)                
                    addr = self.pool.get('res.partner').address_get(cr, uid, [part_id], ['delivery', 'invoice', 'contact','location'])
                    #addr_location=self.pool.get('res.partner').address_get(cr, uid, [part_id])
                    part = self.pool.get('res.partner').browse(cr, uid, part_id)
                    pricelist = part.property_product_pricelist and part.property_product_pricelist.id or False
                    payment_term = part.property_payment_term and part.property_payment_term.id or False
                    fiscal_position = part.property_account_position and part.property_account_position.id or False
                    if ptype['parent_id']:
                        main_prj=self.pool.get('account.analytic.account').browse(cr, uid, ptype['parent_id'][0])
                        try:
                            owner_id=main_prj.partner_id.id
                        except:
                            owner_id=False
                        #raise osv.except_osv("error",str())
                        try:
                            project_e = main_prj.user_id.id
                        except:
                            project_e = False
                        try:
                            project_m = main_prj.prj_m.id
                        except:
                            project_m=False
                        location_addr = self.pool.get('res.partner').address_get(cr, uid, [owner_id], ['location'])['location']
                        val = {'partner_id':part_id,'location':addr['location'],'partner_invoice_id': addr['invoice'],
                            'partner_order_id': addr['contact'],'partner_shipping_id': addr['delivery'], 
                            'payment_term': payment_term,'fiscal_position': fiscal_position,
                            'owner_id':owner_id,'location':location_addr,'project_name':main_prj.name,'currency_id':curr_id,
                            'project_manager_id':project_e,'project_manager_m_id':project_m}
                    else:
                        val = {'partner_id':part_id,'location':addr['location'],'project_name':ptype['name'],
                           'partner_invoice_id': addr['invoice'],'partner_order_id': addr['contact'], 
                           'partner_shipping_id': addr['delivery'], 'payment_term': payment_term, 
                           'fiscal_position': fiscal_position,'currency_id':curr_id}
                else:
                    val={}
            return {'value': val}
    
    def _onchange_owner_id(self, cr, uid, ids, part):
        if not part:
            return {'value': {'location': False}}
        addr = self.pool.get('res.partner').address_get(cr, uid, [part], ['default'])
        val = {'location': addr['default']}
        return {'value': val}
    
    def _onchange_suggest_code(self, cr, uid, ids,new_code):
        if new_code:
            val={'value':{'name':new_code,'newcode_suggest':False}}
        else:
            val={}
        return val
    
    def action_confirm(self, cr, uid, ids, context={}):
        for res in self.read(cr,uid,ids):
            chart_id = res['id']
            if chart_id > 0:
                mod_obj = self.pool.get('ir.module.module')
                mod_obj.button_install(cr, uid, [chart_id], context=context)
        cr.commit()
        db, pool = pooler.restart_pool(cr.dbname, update_module=True)
sale_order()

class sale_order_line(osv.osv):
    _inherit = 'sale.order.line' 
    #Do not touch _name it must be same as _inherit
    _name = 'sale.order.line'
    _description = 'Quotation Line'
sale_order_line()

class init_quotation(osv.osv):
    #Update wkf sale.order.basic on_create false
    #Sale Process False
    _name='init.constraint'
    _auto=False
    
    def init(self,cr):
        #cr.execute("Update process_process set active=False where name='Sales Process'")
        cr.execute("Update wkf set on_create=False where name='sale.order.basic'")
        #Unique Currency Code
        cr.execute("Create or replace view vwnew_electrical_project_code as \
                        Select \
                            pattern || trim(to_char(max(substring(code from length(pattern)+1 for 2)::integer)+1,'00')) as newcode,\
                            name\
                        from\
                            (Select\
                                isq.name,\
                                case when aaa.code is null then prefix || to_char(current_date,'YY-'|| suffix || '00') else aaa.code end as code,\
                                prefix || to_char(current_date,'YY-'|| suffix) as pattern\
                            From \
                                ir_sequence isq\
                            left join\
                                kdvn_project aaa\
                            on aaa.code like (prefix || to_char(current_date,'YY-'|| suffix) || '__')\
                            Where \
                                isq.code='kdvn_electrical_project_code'  and isq.active) vwnewcode\
                        group by pattern,name;")
        cr.execute("Create or replace view vwnew_mechanical_project_code as \
                        Select \
                            pattern || trim(to_char(max(substring(code from length(pattern)+1 for 2)::integer)+1,'00')) as newcode,\
                            name\
                        from\
                            (Select\
                                isq.name,\
                                case when aaa.code is null then prefix || to_char(current_date,'YY-'|| suffix || '00') else aaa.code end as code,\
                                prefix || to_char(current_date,'YY-'|| suffix) as pattern\
                            From \
                                ir_sequence isq\
                            left join\
                                kdvn_project aaa\
                            on aaa.code like (prefix || to_char(current_date,'YY-'|| suffix) || '__')\
                            Where \
                                isq.code='kdvn_mechanical_project_code'  and isq.active) vwnewcode\
                        group by pattern,name;")
        cr.execute("select * from pg_constraint where conname='cannot_duplicate_currency_code'")
        res=cr.fetchall()
        if res is None or not res:
            cr.execute("ALTER TABLE res_currency ADD CONSTRAINT cannot_duplicate_currency_code UNIQUE (name)")
            
#        cr.execute("Select tgname from pg_trigger where tgname='trgchange_approved_amount'");
#        res=cr.fetchall()
#        if res:
#            cr.execute("drop TRIGGER trgchange_approved_amount on sale_order")
#        
#        cr.execute("CREATE OR REPLACE FUNCTION trgfnupdate_approved_quotation()\
#                        RETURNS trigger AS\
#                        $BODY$\
#                        Begin\
#                            If TG_OP='UPDATE' then\
#                                if new.approved_amount<>old.approved_amount then\
#                                    if new.approved_amount=0 or new.approved_amount is null then\
#                                        update sale_order set negotiating='Negotiated' where id=new.id;\
#                                    else\
#                                        update sale_order set negotiating='Fixed' where id=new.id;\
#                                    end if;\
#                                end if;\
#                if new.amount<>old.amount or new.amount_tax <> old.amount_tax then\
#                    update sale_order set amount_total=new.amount+new.amount_tax where id=new.id;\
#                end if;\
#                                if new.q_amount_e<>old.q_amount_e or new.q_amount_e_other <> old.q_amount_e_other or new.q_amount_e_discount<>old.q_amount_e_discount then\
#                    update sale_order set q_amount_total_e=new.q_amount_e+new.q_amount_e_other+q_amount_e_discount where id=new.id;\
#                end if;\
#                                if new.q_amount_m<>old.q_amount_m or new.q_amount_m_other <> old.q_amount_m_other or new.q_amount_m_discount<>old.q_amount_m_discount then\
#                    update sale_order set q_amount_total_m=new.q_amount_m+new.q_amount_m_other+q_amount_m_discount where id=new.id;\
#                end if;                \
#                            end if;\
#                            if TG_OP='INSERT' then\
#                                if new.approved_amount>0 then\
#                                    update sale_order set negotiating='Fixed' where id=new.id;\
#                                else\
#                                    update sale_order set negotiating='Negotiated' where id=new.id;\
#                                end if;\
#                            end if;\
#                        Return new;\
#                        end;    \
#                        $BODY$\
#                      LANGUAGE plpgsql VOLATILE\
#                      COST 100;")
#        
#        cr.execute('CREATE TRIGGER "trgchange_approved_amount"\
#                      AFTER INSERT OR UPDATE\
#                      ON sale_order\
#                      FOR EACH ROW\
#                      EXECUTE PROCEDURE trgfnupdate_approved_quotation();');

init_quotation()

class kdvn_contract_client(osv.osv):
    _name = "kdvn.contract.client"
    _description="KDVN Contract Client"
    _inherit='kdvn.contract.client'
    
    def _get_project_lists(self, cr, uid, ids, name, arg, context=None):
        res = {}
        for id in ids:
            cr.execute('SELECT project_e_id FROM sale_order WHERE contract_id=%s \
                        union\
                        SELECT project_m_id FROM sale_order WHERE contract_id=%s' % (id,id))
            res[id] = [x[0] for x in cr.fetchall() if x[0]]
        return res
    
    _columns={
              #'project_ids':fields.many2many('kdvn.project','kdvn_project_contract_rel','contract_id','project_id','Contracts'),
              'project_ids':fields.function(_get_project_lists,relation='kdvn.project',type='one2many',string='Projects',method=True),
              }
    def init(self,cr):
        #tools.sql.drop_view_if_exists(cr,'kdvn_project_contract_rel')
        cr.execute("create or replace view kdvn_project_contract_rel as\
                        Select \
                            contract_id,\
                            project_e_id as project_id\
                        from \
                            sale_order\
                        where \
                            state<>'cancel' and contract_id>0 and project_e_id>0\
                        union\
                        Select \
                            contract_id,\
                            project_m_id as project_id\
                        from \
                            sale_order \
                        where \
                            state<>'cancel' and contract_id>0 and project_m_id>0")
kdvn_contract_client()